﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	public class frmDSLaoDongVaQuyLuongTrichNopBH : System.Windows.Forms.Form
	{
        private AccessData mDB = new AccessData();
		private DataSet ds =new DataSet();
		private DataSet dsxml =new DataSet();
		double tongquy=0,bhxh=0;
        string s_bophan = "", user_dn = "";
		
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Button butKetThuc;
		private System.Windows.Forms.Button butBaoCao;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.RadioButton rdbMayIn;
		private System.Windows.Forms.RadioButton rdbManHinh;
		private System.Windows.Forms.TextBox txbNam;
		private System.Windows.Forms.RadioButton rdbExcel;
		private System.ComponentModel.Container components = null;

		public frmDSLaoDongVaQuyLuongTrichNopBH(string s_makp,string s_userdn)
		{
			InitializeComponent();
            s_bophan = s_makp;
            user_dn = s_userdn;
		}

		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmDSLaoDongVaQuyLuongTrichNopBH));
            this.label3 = new System.Windows.Forms.Label();
            this.butKetThuc = new System.Windows.Forms.Button();
            this.butBaoCao = new System.Windows.Forms.Button();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.rdbExcel = new System.Windows.Forms.RadioButton();
            this.rdbMayIn = new System.Windows.Forms.RadioButton();
            this.rdbManHinh = new System.Windows.Forms.RadioButton();
            this.txbNam = new System.Windows.Forms.TextBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.Location = new System.Drawing.Point(52, 28);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(40, 16);
            this.label3.TabIndex = 19;
            this.label3.Text = "Năm ";
            // 
            // butKetThuc
            // 
            this.butKetThuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butKetThuc.Image = global::Human.Properties.Resources.close_r;
            this.butKetThuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butKetThuc.Location = new System.Drawing.Point(136, 124);
            this.butKetThuc.Name = "butKetThuc";
            this.butKetThuc.Size = new System.Drawing.Size(75, 28);
            this.butKetThuc.TabIndex = 20;
            this.butKetThuc.Text = "Kết thúc";
            this.butKetThuc.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butKetThuc.Click += new System.EventHandler(this.butKetThuc_Click);
            // 
            // butBaoCao
            // 
            this.butBaoCao.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butBaoCao.Image = global::Human.Properties.Resources.t_search;
            this.butBaoCao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butBaoCao.Location = new System.Drawing.Point(55, 124);
            this.butBaoCao.Name = "butBaoCao";
            this.butBaoCao.Size = new System.Drawing.Size(75, 28);
            this.butBaoCao.TabIndex = 17;
            this.butBaoCao.Text = "Báo cáo";
            this.butBaoCao.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butBaoCao.Click += new System.EventHandler(this.butBaoCao_Click);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.rdbExcel);
            this.groupBox1.Controls.Add(this.rdbMayIn);
            this.groupBox1.Controls.Add(this.rdbManHinh);
            this.groupBox1.Location = new System.Drawing.Point(12, 60);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(232, 41);
            this.groupBox1.TabIndex = 16;
            this.groupBox1.TabStop = false;
            // 
            // rdbExcel
            // 
            this.rdbExcel.AutoSize = true;
            this.rdbExcel.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbExcel.Location = new System.Drawing.Point(164, 14);
            this.rdbExcel.Name = "rdbExcel";
            this.rdbExcel.Size = new System.Drawing.Size(59, 20);
            this.rdbExcel.TabIndex = 3;
            this.rdbExcel.Text = "Excel";
            this.rdbExcel.CheckedChanged += new System.EventHandler(this.rdbExcel_CheckedChanged);
            // 
            // rdbMayIn
            // 
            this.rdbMayIn.AutoSize = true;
            this.rdbMayIn.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbMayIn.Location = new System.Drawing.Point(93, 14);
            this.rdbMayIn.Name = "rdbMayIn";
            this.rdbMayIn.Size = new System.Drawing.Size(65, 20);
            this.rdbMayIn.TabIndex = 2;
            this.rdbMayIn.Text = "Máy in";
            // 
            // rdbManHinh
            // 
            this.rdbManHinh.AutoSize = true;
            this.rdbManHinh.Checked = true;
            this.rdbManHinh.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.rdbManHinh.Location = new System.Drawing.Point(8, 14);
            this.rdbManHinh.Name = "rdbManHinh";
            this.rdbManHinh.Size = new System.Drawing.Size(79, 20);
            this.rdbManHinh.TabIndex = 1;
            this.rdbManHinh.TabStop = true;
            this.rdbManHinh.Text = "Màn hình";
            // 
            // txbNam
            // 
            this.txbNam.ForeColor = System.Drawing.Color.Blue;
            this.txbNam.Location = new System.Drawing.Point(115, 25);
            this.txbNam.MaxLength = 4;
            this.txbNam.Name = "txbNam";
            this.txbNam.Size = new System.Drawing.Size(64, 20);
            this.txbNam.TabIndex = 21;
            this.txbNam.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txbNam_KeyPress);
            this.txbNam.TextChanged += new System.EventHandler(this.txbNam_TextChanged);
            // 
            // frmDSLaoDongVaQuyLuongTrichNopBH
            // 
            this.ClientSize = new System.Drawing.Size(269, 173);
            this.Controls.Add(this.txbNam);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.butKetThuc);
            this.Controls.Add(this.butBaoCao);
            this.Controls.Add(this.groupBox1);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmDSLaoDongVaQuyLuongTrichNopBH";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Lao động và quỹ lương trích nộp BHXH";
            this.Load += new System.EventHandler(this.frmDSLaoDongVaQuyLuongTrichNopBH_Load);
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void butKetThuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private bool IsNumeric (string strNum)
		{	
			int iNum;
			try
			{
				iNum = int.Parse(strNum);
				return true;
			}
			catch
			{
				return false;
			}
		}

		private void print(int prn)
		{			
			string sql="",m_user="";
            m_user = mDB.user;            
			DataSet dstmp = new DataSet();
			ExportToExcel exel = new ExportToExcel();
			
			sql="SELECT '"+txbNam.Text.ToString()+"' as nam , a.manv, Trim(a.ho)||' '||Trim(a.ten) AS hoten ";
            sql += " , b.sobhxh, b.c_thich_cv as cdnn,b.hs_lc AS lcb,(b.hs_lc)*round(b.hs_vk/100,3) AS m_vk,b.hs_cv";
			sql += " ,(b.hs_cv+round(b.hs_lc*b.hs_vk/100,3)) AS tso,(b.hs_lc+b.hs_cv+round(b.hs_lc*b.hs_vk/100,3))*b.luonghd AS tongquyluong,d.tenbv, ";
			sql += " case when a.phai='Nam' then a.namsinh else '' end AS nsnam ";
            sql += " ,case when a.phai='Nữ' then a.namsinh else '' end AS nsnu";
            sql += " ,b.hs_kv AS tdokv,b.luonghd*(b.hs_lc+b.hs_cv+b.hs_kv)/5 AS tsotienbhxh";
            sql += " FROM " + m_user + ".llnv AS a INNER JOIN " + m_user + ".hopdong AS b ON a.manv = b.manv  ";
            sql += " LEFT JOIN " + m_user + ".thebhyt AS c ON b.manv = c.manv";
            sql += " LEFT JOIN " + m_user + ".benhvien AS d ON c.tai_bvien = d.mabv";
            sql += " LEFT JOIN " + m_user + ".dmcd e ON a.macd = e.ma";
            sql += " inner join "+m_user+".max_lan ml on b.manv=ml.manv";
            sql += " WHERE b.ngaybhxh<=to_timestamp('31/12/" + txbNam.Text.ToString() + "','dd/mm/yyyy')";
            
            sql+=" and (b.ngaynghi is null or (b.ngaynghi is not null ";
            sql += " and b.ngaynghi>to_timestamp('31/12/" + txbNam.Text.Trim() + "','dd/mm/yyyy')))";//" and a.manv in ('00769','00770','00771','00772','00773','00774','00775','00776','00777','00778','00779','00780')";
            sql += " and b.lan=ml.lan";
            sql+=" ORDER BY  a.manv,b.sobhxh ";
			
			ds=mDB.get_data(sql);			
			ds.WriteXml("..\\xml\\BaoCaobhxh.xml",XmlWriteMode.WriteSchema);			
			if (ds.Tables[0].Rows.Count>0)
			{
				switch (prn)
				{                   
					case 1:	
						frmReport f=new frmReport(mDB,ds,"DANH SÁCH LAO ĐỘNG VÀ QUỸ TIỀN LƯƠNG TRÍCH NỘP BHXH","r_BaoCaobhxh.rpt",true,user_dn);
						f.ShowDialog(this);
						break;
					case 3:
						dstmp=ket_xuat_excel(ds);
						exel.exp_excel_quyluong(dstmp,"dsldqtl"+txbNam.Text,txbNam.Text,tongquy.ToString("#,##0.00"),bhxh.ToString("#,##0.00"));
						break;
				}
			}
			else
				MessageBox.Show("Chưa có đầy đủ thông tin","Thông Báo");
		}
		private DataSet ket_xuat_excel(DataSet dst )
		{
			string[] str = new string[]{"STT","Họ Và Tên","Số sổ BHXH","Nam","Nữ","Lương cơ bản","Vượt khung","P. Cấp chức vụ","Tổng số 23%","Nơi ĐKKCB","Ghi chú"};
			DataSet ds1 = new DataSet();
			ds1.Tables.Add();
			ds1.Tables[0].Columns.Add("STT",typeof(string));
			ds1.Tables[0].Columns.Add("hoten",typeof(string));
			ds1.Tables[0].Columns.Add("sobhxh",typeof(string));
			ds1.Tables[0].Columns.Add("nsnam",typeof(string));
			ds1.Tables[0].Columns.Add("nsnu",typeof(string));
			ds1.Tables[0].Columns.Add("cdnn",typeof(string));
			ds1.Tables[0].Columns.Add("lcb",typeof(string));
			ds1.Tables[0].Columns.Add("hsvk",typeof(string));
			ds1.Tables[0].Columns.Add("hscv",typeof(string));
			ds1.Tables[0].Columns.Add("tongquy",typeof(string));
			ds1.Tables[0].Columns.Add("tenbv",typeof(string));
			ds1.Tables[0].Columns.Add("Ghichu",typeof(string));
			DataRow nrow;
			int stt=0;
			double tq=0,SUMTQ=0,SUMLCB=0,SUMVK=0,SUMHSCV=0; 
			foreach(DataRow r in dst.Tables[0].Rows)
			{
				stt++;
				nrow = ds1.Tables[0].NewRow();
				nrow["stt"]=stt;
				nrow["hoten"]=mDB.Caps(r["hoten"].ToString().ToLower());
				nrow["sobhxh"]=r["sobhxh"].ToString();
				nrow["nsnam"]=r["nsnam"].ToString();
				nrow["nsnu"]=r["nsnu"].ToString();
				nrow["cdnn"]=r["cdnn"].ToString();
				nrow["lcb"]=r["lcb"].ToString();
				nrow["hsvk"]=r["m_vk"].ToString();
				nrow["hscv"]=r["hs_cv"].ToString();				
				tq=(r["tongquyluong"].ToString()!="")?double.Parse(r["tongquyluong"].ToString())*0.23:0;
				tongquy+=(r["tongquyluong"].ToString()!="")?double.Parse(r["tongquyluong"].ToString()):0;

				nrow["tongquy"]= tq.ToString("#,##0.00");
				nrow["tenbv"]= r["tenbv"].ToString();
				nrow["Ghichu"]=" ";
				SUMLCB+=(r["lcb"].ToString()!="")?double.Parse(r["lcb"].ToString()):0;
				SUMVK+=(r["m_vk"].ToString()!="")?double.Parse(r["m_vk"].ToString()):0;
				SUMHSCV+=(r["hs_cv"].ToString()!="")?double.Parse(r["hs_cv"].ToString()):0;
				SUMTQ+=tq;
				ds1.Tables[0].Rows.Add(nrow);
			}
			bhxh=SUMTQ;
			nrow = ds1.Tables[0].NewRow();
			nrow["hoten"]=" TỔNG CỘNG: ";
			nrow["lcb"]=SUMLCB.ToString("#,##0.00");
			nrow["hsvk"]=SUMVK.ToString("#,##0.00");
			nrow["hscv"]=SUMHSCV.ToString("#,##0.00");
			nrow["tongquy"]= SUMTQ.ToString("#,##0.00");
			ds1.Tables[0].Rows.Add(nrow);
			return ds1;
		}
		private void txbNam_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			if (!IsNumeric(e.KeyChar.ToString()))
				e.Handled = true;
			if(e.KeyChar==8)
				e.Handled=false;
		}

		private void butBaoCao_Click(object sender, System.EventArgs e)
		{
            try
            {
                if (Convert.ToInt32(txbNam.Text) > DateTime.Now.Year)
                {
                    MessageBox.Show("Năm báo cáolớn hơn năm hiện tại", "Thông Báo", MessageBoxButtons.OK);
                    txbNam.Focus();
                }
                else
                {
                    tongquy = 0;
                    bhxh = 0;
                    if (rdbManHinh.Checked == true) print(1);
                    if (rdbMayIn.Checked == true) print(2);
                    if (rdbExcel.Checked) print(3); 
                }
            }
            catch
            {
            }			
		}
		private void frmDSLaoDongVaQuyLuongTrichNopBH_Load(object sender, System.EventArgs e)
		{
			DateTime dt=DateTime.Now,dt1;
			dt1=dt.AddYears(1);
			txbNam.Text=dt.Year.ToString();
		}
        private void txbNam_TextChanged(object sender, EventArgs e)
        {
            
        }

        private void rdbExcel_CheckedChanged(object sender, EventArgs e)
        {

        }
	}
}
